# These module provide data import and export
import csv
import os
from db.mongodb import DBConn, Database

class DataConv:
    ''' Import datas from to the database
    '''
    def __init__(self, db=None):
        """
        Arguments:
            db --- DBConnection object
        """
        self.db = db
        if db is not None:
            self.db_name = db.db_name
    
    def insert_to_db(self, item):
        #user.items.append(item)
        db.session.add(item)
        db.session.commit()
        
    # table: table name of relational database, or collection of mongodb
    def export_to(self, table, fname, delimiter=';'):
        _format = os.path.splitext(fname)[1][1:].lower()
    
    def load(self, fname, _delimiter=r';'):
        '''Load head and rows from the source file( default is csv file) to a buffer
        '''
        _format = os.path.splitext(fname)[1][1:].lower()
        if _format=='csv':
            # Get contents from the csv file
            with open(fname, 'rb') as f:    
                try:
                    reader = csv.reader(f, delimiter=_delimiter, quotechar='|')
                    
                    _list = []
                    for row in reader:
                        _list.append(row)
                    
                    self.head = []
                    for cell in _list[0]:
                        self.head.append(cell.strip())
                    
                    self.rows = []
                    for row in _list[1:]:
                        item = []
                        for cell in row:
                            item.append(cell.strip())
                        self.rows.append(item)
                finally:
                    f.close()
                
    def import_all(self, MyClass=None, fname=None, _delimiter=r';',ignore_list=[]):
        ''' Import all the columns from csv file to the database.table
        '''
        self.load(fname, _delimiter)
        
        for row in self.rows:
            _dict = {}
            i = 0
            for cell in row:
                if self.head[i] not in ignore_list:
                    _dict[self.head[i]] = cell
                i += 1
            #self.db.insert(_dict, table)
            db.session.add(MyClass(_dict['city'], _dict['province']))
            #db.session.add(MyClass(_dict['name'], _dict['description'],_dict['parent_id']))
        db.session.commit()    
        print 'import data from {0} to table {1} --- done'.format(fname, MyClass)
    
    def import2(self, table, fname):
        ''' The csv file can't be create manually, because it has the id object column for reference, it was
        generated usually.
        
        The title of the reference has special pattern, usually table_name + '_ref', from this column, we will
        get the reference table name and id
        '''
        pass
        # Analyse table name, get reference table name
        

        #product_ref = DBRef('products', id, self.db_name) # id the ObjectID
            
        #self.db.insert({'path':path,'product_ref':product_ref},'picutures')
        
        
    # Private call only
    def _abbrev_province(self, fname):
        '''Convert province column to abbrevation
        '''
        provinces = {'Alberta':'AB',
                    'British Columbia':'BC',
                    'Manitoba':'MB',
                    'New Brunswick':'NB',
                    'Newfoundland':'NL',
                    'Northwest Territories':'NT',
                    'Nova Scotia':'NS',
                    'Nunavut':'NU',
                    'Ontario':'ON',
                    'Prince Edward Island':'PE',
                    'Quebec':'QC',
                    'Saskatchewan':'SK',
                    'Yukon':'YT'}
        
        with open(fname, 'wb') as f:    
            try:
                parser = csv.writer(f, delimiter=';', quotechar='|')
                parser.writerow(self.head)
                keys = provinces.keys()
                for cells in self.rows:
                    cells[0] = cells[0].strip()
                    cells[1] = cells[1].strip()
                    if cells[1] in keys:
                        cells[1] = provinces[cells[1]]
                        parser.writerow(cells)
            finally:
                f.close()
            
if __name__ == '__main__':
    '''
    from lib.database import DBConnection
    root_path = os.environ['FANSHAWN_HOME']
    conn = DBConnection()
    '''
    from src.model.city import City
    from src.model.category import Category
    conv = DataConv()
    conv.import_all(City, 'cities.csv')
    #conv.import_all(Category, 'categories.csv')
    print 'convert done!'

    '''
    def load_cities():
        fname = os.path.join(root_path, r'cities.csv')
        conv = DataConv(conn)
        conv.load(fname,',')
        conv._abbrev_province(os.path.join(root_path, r'data', r'cities2.csv'))
    
    
    def load_products():
        fname = os.path.join(root_path, r'tools\products.csv')
        conv = DataConv(conn)
        conv.import_all('products', fname)    

    def load_users():
        fname = os.path.join(root_path, r'tools\users.csv')
        conv = DataConv(conn)
        conv.import_all('users', fname, ',', ignore_list=['street','postcode','city','province'])    

    def load_pictures():
        product_ref = DBRef('products', id, self.db_name) # id the ObjectID
            
        self.db.insert({'path':path,
                        'product_ref':product_ref},'picutures')
    '''
    
    #load_products()
    #load_users()   